package br.com.portal.dao;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.SQLException;

import br.com.portal.beans.Transportadora;
import br.com.portal.factory.ConnectionFactory;

import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class TransportadoraDAO {
	public static List<Transportadora> buscarTransportadora(String q, Integer start, Integer max) throws Exception {
		List<Transportadora> transportadoras = new ArrayList<Transportadora>();
		Connection conn = ConnectionFactory.getConnection();
		
		String sql = "SELECT * FROM transportadora WHERE nome like ? LIMIT ? OFFSET ?";

		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, '%'+q.toUpperCase()+'%');
		stmt.setInt(2, max);
		stmt.setInt(3, start);
		
		ResultSet rs = stmt.executeQuery();

		while(rs.next()) {
			Transportadora transportadora = new Transportadora();
			transportadora.setCodigo(rs.getString("codigo"));
			transportadora.setNome(rs.getString("nome"));
			transportadora.setEstado(rs.getString("estado"));

			transportadoras.add(transportadora);
		}
		
		return transportadoras;
	}
	
	public static Integer totalTransportadora(String q) throws Exception {
		Integer totalCount;
		Connection conn = ConnectionFactory.getConnection();
		
		String sql = "SELECT count(*) as total FROM transportadora WHERE nome like ?";

		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, '%'+q.toUpperCase()+'%');

		ResultSet rs = stmt.executeQuery();
		rs.next();
		totalCount = Integer.parseInt(rs.getString("total"));
		
		return totalCount;
	}
}